package models;

import java.util.*;

import javax.persistence.*;

import com.avaje.ebean.Ebean;
import com.avaje.ebean.SqlQuery;
import com.avaje.ebean.SqlRow;

import play.db.ebean.*;
import play.data.format.*;
import play.data.validation.*;



public class AdminGraph extends Model {

	public static final long serialVersionUID = 1L;

	
    public String speciality;
    
    public String month;
    
    public String year;
    
    public Double amount;

        public static Model.Finder<String,AdminGraph> find = new Model.Finder(String.class, AdminGraph.class);

        public static List<AdminGraph> getDepartmentMonth () {
        	List<AdminGraph> amounts = new ArrayList<AdminGraph>();
        	String sql = "SELECT speciality,monthname(date_of_appointment) as month, year(date_of_appointment) as year, sum(payment_amount) as amount FROM appment.appointment_history  ah join doctor_details dd on ah.idDoctor = dd.doctor_id join speciality s on s.idspeciality = dd.idspeciality where payment_amount is not null and YEAR(date_of_appointment) = YEAR(CURDATE()) and month(date_of_appointment) = month(CURDATE()) group by  year(date_of_appointment),month(date_of_appointment),speciality";
        	SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
        //	sqlQuery.setParameter("id", id);
        	List<SqlRow> rows = sqlQuery.findList();
        //	List<String> names= new ArrayList<String>(rows.size());
        	AdminGraph admin = new AdminGraph();
            for (SqlRow row : rows){
            	admin.month = row.getString("month");
            	admin.speciality = row.getString("speciality");
            	admin.amount = new Double(row.getString("amount"));
            	amounts.add(admin);
            	admin = new AdminGraph();
            }

             return amounts;
        }   
        
        public static List<AdminGraph> getYearlyRevenue() {
        	List<AdminGraph> amounts = new ArrayList<AdminGraph>();
        	String sql = "SELECT  year(date_of_appointment) as year, sum(payment_amount) as amount FROM appment.appointment_history  ah where payment_amount is not null group by  year(date_of_appointment)";
        	SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
        //	sqlQuery.setParameter("id", id);
        	List<SqlRow> rows = sqlQuery.findList();
        //	List<String> names= new ArrayList<String>(rows.size());
        	AdminGraph admin = new AdminGraph();
            for (SqlRow row : rows){
            	admin.year = row.getString("year");
            	admin.amount = new Double(row.getString("amount"));
            	amounts.add(admin);
            	admin = new AdminGraph();
            }

             return amounts;
        }   
}

